//
//  AutodyneDAO.m
//  MyShoes
//
//  Created by mac on 11-6-3.
//  Copyright 2011 __MyCompanyName__. All rights reserved.
//

#import "NetPhoneDAO.h"
#import "GlobalVar.h"
#import <AddressBook/AddressBook.h>
#import "Contact.h"
#import "CallRecord.h"

#define ALPHANUM @"0123456789"
#define ALPHA @"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
#define ALPHAPOSITION @"22233344455566677778889999"

sqlite3* database_;

@implementation NetPhoneDAO

static NetPhoneDAO *shareNetPhoneDAOInstance = nil;

+(NetPhoneDAO *) sharedInstance{
    @synchronized(self){
        if (shareNetPhoneDAOInstance == nil) {
            shareNetPhoneDAOInstance = [[self alloc]init];
        }
    }
    return shareNetPhoneDAOInstance;
}

-(void)setupDB
{
	if (database_) 
	{
		return;
	}
    
    
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSString *writableDBPath = [GlobalVar getFilePath:@"main_db.sqlite"];
    BOOL success = [fileManager fileExistsAtPath:writableDBPath];
    if (!success) {
        return;
    }
    
	if(sqlite3_open([writableDBPath UTF8String], &database_) != SQLITE_OK) {
        sqlite3_close(database_);
        NSLog(@"Error: open database file.");
    }
    
}
-(void)closeDB
{
    //sqlite3_close(database_);
    //database_= nil;
}

-(NSString *)getColumnText:(char *)_char
{
    if(_char){
        return [NSString stringWithUTF8String:_char];
    }else{
        return @"";
    }
}

-(NSArray *)loadContactByCode:(NSString *)code equals:(BOOL)isEquals
{
	[self setupDB];
    
    NSMutableArray *result = [NSMutableArray array];
    //ABAddressBookRef addressBook = addressBook = ABAddressBookCreate();
    ABAddressBookRef addressBook = ABAddressBookCreate();
    sqlite3_stmt *statement = nil;
    NSString *sql;
    if (isEquals) {
        sql = [NSString stringWithFormat:@"SELECT record_id FROM addressbook_index where source = '%@' ",code];
    }else{
        sql = [NSString stringWithFormat:@"SELECT record_id FROM addressbook_index where code like '%%%@%%' group by record_id",code];
    }
    //NSLog(@"%@",sql);
    if (sqlite3_prepare_v2(database_, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            int record_id = sqlite3_column_int(statement, 0);
            ABRecordRef person = ABAddressBookGetPersonWithRecordID(addressBook,record_id);
            
            if (person) {
                NSString *firstName = (NSString*)ABRecordCopyValue(person, kABPersonFirstNameProperty);
                NSString *lastName = (NSString*)ABRecordCopyValue(person, kABPersonLastNameProperty);
                NSString *name = @"";
                if (firstName) {
                    if (lastName) {
                        name = [NSString stringWithFormat:@"%@ %@",lastName,firstName];
                    }else{
                        name = firstName;
                    }
                }else{
                    if (lastName) {
                        name = lastName;
                    }
                }
                
                ABMultiValueRef phone = ABRecordCopyValue(person, kABPersonPhoneProperty);
                for (int k = 0; k<ABMultiValueGetCount(phone); k++)
                {
                    NSString * personPhone = (NSString*)ABMultiValueCopyValueAtIndex(phone, k);
                    personPhone = [personPhone stringByReplacingOccurrencesOfString:@"-" withString:@""];
                    personPhone = [personPhone stringByReplacingOccurrencesOfString:@"+" withString:@""];
                    
                    Contact *contact = [[[Contact alloc]init]autorelease];
                    contact.name = name;                
                    contact.phone = personPhone;
                    [result addObject:contact];
                }
            }else{
                
                sqlite3_stmt *delete_statement= nil;
                NSString *sql_delete = [NSString stringWithFormat:@"delete FROM addressbook_index where record_id=%d",record_id];
                int success = sqlite3_prepare_v2(database_, [sql_delete UTF8String], -1, &delete_statement, NULL);
                if (success != SQLITE_OK) {
                    NSLog(@"Error: failed to prepare delete");
                }
                success = sqlite3_step(delete_statement);
                if (success == SQLITE_ERROR) {
                    NSLog(@"Error: failed to delete.");
                }
                sqlite3_finalize(delete_statement);
                
            }
            
        }
    }
    sqlite3_finalize(statement);
    [self closeDB];
    CFRelease(addressBook);
    
    return  result;
}


-(void)initNetPhoneData:(NSObject<InitNetPhoneDataDelegate> *)delegate
{
	[self setupDB];

    sqlite3_stmt *statement = nil;
    NSString *sql = [NSString stringWithFormat:@"SELECT max(record_id) FROM addressbook_index"];
    int max_record_id = 0 ;
    if (sqlite3_prepare_v2(database_, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
        if (sqlite3_step(statement) == SQLITE_ROW) {
             max_record_id = sqlite3_column_int(statement, 0);
        }
    }
    sqlite3_finalize(statement);
    //NSLog(@"%d",max_record_id);

    
    ABAddressBookRef addressBook = addressBook = ABAddressBookCreate();
    CFArrayRef results = ABAddressBookCopyArrayOfAllPeople(addressBook);
    int count = CFArrayGetCount(results);
    for(int i = 0; i < count; i++)
    {
        if (delegate) {
            NSArray *progress = [NSArray arrayWithObjects:[NSNumber numberWithInt:i],[NSNumber numberWithInt:count], nil];
            [delegate performSelectorOnMainThread:@selector(initNetPhoneDataProgress:) withObject:progress waitUntilDone:YES];
        }
        
        
        ABRecordRef person = CFArrayGetValueAtIndex(results, i);
        NSNumber *recordId = [NSNumber numberWithInteger: ABRecordGetRecordID(person)];
        if ([recordId intValue] <= max_record_id) {
            continue;
        }
        
        
        NSString *firstName = (NSString*)ABRecordCopyValue(person, kABPersonFirstNameProperty);
        NSString *lastName = (NSString*)ABRecordCopyValue(person, kABPersonLastNameProperty);
        NSString *name = @"";
        if (firstName) {
            if (lastName) {
                name = [NSString stringWithFormat:@"%@ %@",lastName,firstName];
             }else{
                name = firstName;
            }
        }else{
            if (lastName) {
                name = lastName;
            }
        }
        //ABRecordRef person = ABAddressBookGetPersonWithRecordID(ab,69283952);
        
        
        NSMutableString *thekey = [NSMutableString string];
        NSMutableString *shortkey = [NSMutableString string];
        
        sqlite3_stmt *statement = nil;
        for (int i=0; i<name.length; i++) {
            NSString *tmpString = [name substringWithRange:NSMakeRange(i, 1)];
            NSCharacterSet *cs = [[NSCharacterSet characterSetWithCharactersInString:ALPHANUM] invertedSet];
            NSString *filtered = [[tmpString componentsSeparatedByCharactersInSet:cs] componentsJoinedByString:@""];
            BOOL basicTest = [tmpString isEqualToString:filtered];
            if (basicTest) {
                [thekey appendString:tmpString];
                [shortkey appendString:tmpString];
            }else{
                cs = [[NSCharacterSet characterSetWithCharactersInString:ALPHA] invertedSet];
                filtered = [[tmpString componentsSeparatedByCharactersInSet:cs] componentsJoinedByString:@""];
                BOOL basicTest = [tmpString isEqualToString:filtered];
                if (basicTest) {
                   
                    NSRange range = [ALPHA rangeOfString:tmpString];
                    if (range.length > 0) {
                        int location = range.location;
                        if (location > 25) {
                            location = location - 25;
                        }
                        if (location<ALPHAPOSITION.length) {
                            [thekey appendString:[ALPHAPOSITION substringWithRange:NSMakeRange(location, 1)]];
                            [shortkey appendString:[ALPHAPOSITION substringWithRange:NSMakeRange(location, 1)]];
                        }

                        //NSLog(@"%@",[ALPHAPOSITION substringWithRange:NSMakeRange(location, 1)]);
                    }
                    
                    
                }else{
                    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM pinyin_mandarin where letter='%@'",tmpString];
                    //NSLog(@"%@",sql);
                    if (sqlite3_prepare_v2(database_, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
                        
                        if (sqlite3_step(statement) == SQLITE_ROW) {
                            NSString *keypad = [self getColumnText:(char*)sqlite3_column_text(statement, 3)];
                            [thekey appendString:keypad];
                            [shortkey appendString:[keypad substringToIndex:1]];
                        }
                    }  
                }
            }
            
            if (i<name.length - 1) {
                [thekey appendString:@"#"];
                [shortkey appendString:@"#"];
            }
        }
        sqlite3_finalize(statement);
        //NSLog(@"%d--%@---%@---%@",[recordId intValue],name,thekey,shortkey);
        
        sqlite3_stmt *insert_statement = nil;
        NSString *sql = @"insert into addressbook_index (code,syllable,record_id,div,source) values(?,?,?,?,?)";
        int success = sqlite3_prepare_v2(database_, [sql UTF8String], -1, &insert_statement, NULL);
        if (success != SQLITE_OK) {
            NSLog(@"Error: failed to insert prepare");
            return;
        }
        if (name && ![name isEqualToString:@""]) {
            sqlite3_bind_text(insert_statement, 1, [[thekey stringByReplacingOccurrencesOfString:@"#" withString:@""] UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(insert_statement, 2, [thekey UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(insert_statement, 3, [recordId intValue]);
            sqlite3_bind_int(insert_statement, 4, 10);
            sqlite3_bind_text(insert_statement, 5, [name UTF8String], -1, SQLITE_TRANSIENT);
            success = sqlite3_step(insert_statement);
            
            sqlite3_prepare_v2(database_, [sql UTF8String], -1, &insert_statement, NULL);
            sqlite3_bind_text(insert_statement, 1, [[shortkey stringByReplacingOccurrencesOfString:@"#" withString:@""] UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(insert_statement, 2, [shortkey UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(insert_statement, 3, [recordId intValue]);
            sqlite3_bind_int(insert_statement, 4, 12);
            sqlite3_bind_text(insert_statement, 5, [name UTF8String], -1, SQLITE_TRANSIENT);
            success = sqlite3_step(insert_statement);
            
        }
        
        
        
        ABMultiValueRef phone = ABRecordCopyValue(person, kABPersonPhoneProperty);
        
        for (int k = 0; k<ABMultiValueGetCount(phone); k++)
        {
            //NSString * personPhoneLabel = (NSString*)ABAddressBookCopyLocalizedLabel(ABMultiValueCopyLabelAtIndex(phone, k));
            NSString * personPhone = (NSString*)ABMultiValueCopyValueAtIndex(phone, k);
            personPhone = [personPhone stringByReplacingOccurrencesOfString:@"-" withString:@""];
            personPhone = [personPhone stringByReplacingOccurrencesOfString:@"+" withString:@""];
            // NSLog(@"%@:%@",personPhoneLabel,personPhone);

            sqlite3_prepare_v2(database_, [sql UTF8String], -1, &insert_statement, NULL);
            sqlite3_bind_text(insert_statement, 1, [personPhone UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(insert_statement, 2, [personPhone UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(insert_statement, 3, [recordId intValue]);
            sqlite3_bind_int(insert_statement, 4, 22);
            sqlite3_bind_text(insert_statement, 5, [personPhone UTF8String], -1, SQLITE_TRANSIENT);
            success = sqlite3_step(insert_statement);

        }
        
        sqlite3_finalize(insert_statement);
      
        CFRelease(phone);
        [firstName release];
        [lastName release];
       
    }
    CFRelease(results);
    CFRelease(addressBook);
    
    
    [self closeDB];
}

-(NSArray *)loadAllCallRecrod
{
	[self setupDB];
    NSMutableArray *result = [NSMutableArray array];
    sqlite3_stmt *statement = nil;
    NSString *sql = @"SELECT phone_num,phone_name,max(call_date),max(call_date||call_time),count(*) FROM call_record group by phone_num,phone_name order by call_date desc, call_time desc,idx desc";
    if (sqlite3_prepare_v2(database_, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            
            CallRecord *callRecord = [[[CallRecord alloc]init]autorelease];
            callRecord.phoneNum = [self getColumnText:(char*)sqlite3_column_text(statement, 0)];        
            callRecord.phoneName = [self getColumnText:(char*)sqlite3_column_text(statement, 1)];
            callRecord.callDate = [self getColumnText:(char*)sqlite3_column_text(statement, 2)];
            
            NSString *time = [self getColumnText:(char*)sqlite3_column_text(statement, 3)];
            if (time.length > 10) {
                time = [time substringFromIndex:10];
            }
            
            
            callRecord.callTime =time;
            callRecord.count = sqlite3_column_int(statement, 4);
            
            [result addObject:callRecord];
        }
    }
    sqlite3_finalize(statement);
    [self closeDB];
    return  result;
}


-(NSArray *)loadCallRecordWidthNumber:(NSString *)num name:(NSString *)name
{
    [self setupDB];
    NSMutableArray *result = [NSMutableArray array];
    sqlite3_stmt *statement = nil;
    NSString *sql = @"SELECT phone_num,phone_name,call_date,call_time FROM call_record where phone_num ='%@' and phone_name='%@' order by call_date desc,call_time desc";
    sql = [NSString stringWithFormat:sql,num,name];
    if (sqlite3_prepare_v2(database_, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            
            CallRecord *callRecord = [[[CallRecord alloc]init]autorelease];
            callRecord.phoneNum = [self getColumnText:(char*)sqlite3_column_text(statement, 0)];        
            callRecord.phoneName = [self getColumnText:(char*)sqlite3_column_text(statement, 1)];
            callRecord.callDate = [self getColumnText:(char*)sqlite3_column_text(statement, 2)];
            callRecord.callTime = [self getColumnText:(char*)sqlite3_column_text(statement, 3)];
            callRecord.count = sqlite3_column_int(statement, 4);
            
            [result addObject:callRecord];
        }
    }
    sqlite3_finalize(statement);
    [self closeDB];
    return  result;
}



-(void)insertCallRecrod:(NSString *)phoneNum name:(NSString *)name
{
    
     NSLog(@"insertCallRecrod:%@",name);
     NSLog(@"insertCallRecrod:%@",phoneNum);
    
 	[self setupDB];
   
    sqlite3_stmt *insert_statement = nil;
    NSString *sql = @"insert into call_record (phone_num,phone_name,call_date,call_time) values(?,?,?,?)";
    int success = sqlite3_prepare_v2(database_, [sql UTF8String], -1, &insert_statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to insert prepare");
        return;
    }
    
   // NSLog(@"%@",name);
    
    NSDateFormatter *datef=[[[NSDateFormatter alloc] init]autorelease];
    [datef setDateFormat:@"yyyy-MM-dd"];
    NSString *theDate = [datef stringFromDate:[NSDate date]];
    [datef setDateFormat:@"HH:mm"];
    NSString *theTime = [datef stringFromDate:[NSDate date]];
    
    if (name) {
        sqlite3_bind_text(insert_statement, 1, [phoneNum UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insert_statement, 2, [name UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insert_statement, 3, [theDate UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insert_statement, 4, [theTime UTF8String], -1, SQLITE_TRANSIENT);
        success = sqlite3_step(insert_statement);
    }
    
    [self closeDB];
}

-(void)deleteAllCallRecord
{
  	[self setupDB];
   sqlite3_stmt *statement1= nil;
	NSString *sql = @"delete FROM call_record";
    int success = sqlite3_prepare_v2(database_, [sql UTF8String], -1, &statement1, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to prepare delete");
        return;
    }
    success = sqlite3_step(statement1);
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to delete.");
    }
    sqlite3_finalize(statement1);
    [self closeDB];
}

-(void)deleteCallRecrod:(NSString *)phoneNum name:(NSString *)name
{
  	[self setupDB];
    sqlite3_stmt *statement1= nil;
	NSString *sql = [NSString stringWithFormat:@"delete FROM call_record where phone_num ='%@' and phone_name='%@'",phoneNum,name];
    int success = sqlite3_prepare_v2(database_, [sql UTF8String], -1, &statement1, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to prepare delete");
        return;
    }
    success = sqlite3_step(statement1);
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to delete.");
    }
    sqlite3_finalize(statement1);
    [self closeDB];
}


-(void)dealloc
{
    shareNetPhoneDAOInstance = nil;
	sqlite3_close(database_);
	[super dealloc];
}



@end
